﻿#region Librerias
    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;
#endregion

/// <summary>
/// Clase concerniente a la Capa de Acceso a Datos
/// y principal medio de conexion a nuestra Base de Datos
/// </summary>

#region Clase

public class DAC
{
    #region Variables
    
        /// <summary>
        /// cadena de conexion
        /// </summary>
        //const string CNX = "data source=localhost;integrated security=SSPI;initial catalog=w1100905_sbdaDB";
        //const string CNX = "Data Source=JAIME_GASPA;Initial Catalog=sbdaDB;Integrated Security=True";
        //string CNX = ConfigurationManager.ConnectionStrings["SQLSERVERINLINE"].ConnectionString;
        string CNX = ConfigurationManager.ConnectionStrings["SQLSERVERLOCAL"].ConnectionString;

    #endregion

        #region Builder

        /// <summary>
        /// Constructor
        /// </summary>
	    public DAC(){}

    #endregion

    #region Methods

        /* Conection execute
         */
        public SqlConnection OpenConnection()
        {
            SqlConnection con = new SqlConnection(CNX);
            con.Open();
            return con;
        }

        /* Consult execute, recibe la consulta "sql"
         */
        public DataSet ExecuteQuery(string sql)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = this.OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                con.Dispose();
            }
            return ds;
        }

        /* Consult execute, recibe la consulta "sql" y los indices indicadores
         * del tamaño del dataset
         */
        public DataSet ExecuteQuery(string sql, int startRecord, int maxRecord)
        {
            DataSet ds = new DataSet();
            using (SqlConnection con = this.OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds, startRecord, maxRecord, "Logs");
                con.Dispose();
            }
            return ds;
        }
        
        /* Transaction execute, especifica "yes" para recibir el ID del registro insertado,
         * especifica no para recibir la cantidad de filas afectadas
        */
        public int ExecuteDML(string sql, string id)
        {
            using (SqlConnection con = this.OpenConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, con);
                int aux = 0;
                if (id.Equals("yes"))
                {
                   aux = (int)cmd.ExecuteScalar();
                }
                else
                {
                    aux = cmd.ExecuteNonQuery();
                }                
                con.Dispose();
                return aux;
            }
        }

    #endregion
    
}

#endregion


